﻿Imports System.IO
Imports Microsoft.Office.Interop

Public Class Form1

    Private Sub Form1_Activated(sender As Object, e As EventArgs) Handles Me.Activated
        TextBox1.Width = Form1.ActiveForm.Width - 20
        TextBox1.Height = Form1.ActiveForm.Height - 135
        ToolStripTextBoxFile.Text = ""
    End Sub

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        OpenFileDialog1.FileName = ""
    End Sub

    Private Sub Form1_Resize(sender As Object, e As EventArgs) Handles Me.Resize
        Try
            TextBox1.Width = Form1.ActiveForm.Width - 20
            TextBox1.Height = Form1.ActiveForm.Height - 135

        Catch ex As Exception

        End Try
    End Sub

    Private Sub TransformExcelTable()
        Dim objExcel As New Excel.Application
        objExcel.Visible = False
        Dim myWorkbook As Excel.Workbook = Nothing

        ToolStripStatusLabel1.Text = "Reading Excel Workbook"
        Me.Refresh()

        Try

            Dim sFileName As String = ToolStripTextBoxFile.Text
            If sFileName.Length = 0 Then
                Throw New Exception("No file name specified!")
            End If

            If Not File.Exists(sFileName) Then
                Throw New Exception("File not found!")
            End If

            myWorkbook = objExcel.Workbooks.Open(sFileName)
            Dim mySheet As Excel.Worksheet = myWorkbook.Worksheets(1)
            '-------------------------------------------------------------------------------------------------------
            'Get the rows and cols count
            '-------------------------------------------------------------------------------------------------------
            Dim xlCells As Excel.Range = Nothing
            xlCells = mySheet.Cells

            Dim xlTempRange As Excel.Range = xlCells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell)

            Dim iRowsUsed As Integer = xlTempRange.Row
            Dim iColsUsed As Integer = xlTempRange.Column

            Runtime.InteropServices.Marshal.FinalReleaseComObject(xlTempRange)
            xlTempRange = Nothing

            Runtime.InteropServices.Marshal.FinalReleaseComObject(xlCells)
            xlCells = Nothing
            '-------------------------------------------------------------------------------------------------------
            'Read the table
            '-------------------------------------------------------------------------------------------------------
            Dim szRet As String = ""

            For i As Integer = 1 To iColsUsed
                szRet += "| " + mySheet.Cells(1, i).value.ToString.Trim
            Next

            szRet += " |" + vbCrLf

            For i As Integer = 1 To iColsUsed
                szRet += "| --- "

            Next

            szRet += "|" + vbCrLf


            For i As Integer = 2 To iRowsUsed
                For j As Integer = 1 To iColsUsed
                    szRet += "| "
                    If Not IsNothing(mySheet.Cells(i, j).value) Then
                        szRet += mySheet.Cells(i, j).value.ToString.Trim
                    Else
                        szRet += " "
                    End If
                Next
                szRet += "|" + vbCrLf
            Next

            TextBox1.Text = szRet


            myWorkbook.Close()
            objExcel.Quit()

            ReleaseObject(objExcel)
            ReleaseObject(myWorkbook)
            ReleaseObject(mySheet)
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Transform File")
        End Try
    End Sub

    Private Sub ReleaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

    Private Sub ToolStripButton1_Click(sender As Object, e As EventArgs) Handles ToolStripButton1.Click      
        Me.Cursor = Cursors.WaitCursor
        ToolStripStatusLabel1.Text = "Transforming file"
        ToolStripTextBoxFile.Text = OpenFileDialog1.FileName
        TransformExcelTable()
        ToolStripStatusLabel1.Text = "Ready"
        Me.Cursor = Cursors.Default
    End Sub

    Private Sub OpenToolStripButton_Click(sender As Object, e As EventArgs) Handles OpenToolStripButton.Click
        OpenFileDialog1.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)
        OpenFileDialog1.Multiselect = False
        OpenFileDialog1.ValidateNames = True
        OpenFileDialog1.DereferenceLinks = False
        OpenFileDialog1.Filter = "Excel |*.xlsx"
        OpenFileDialog1.FileName = ""

        If OpenFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
            ToolStripTextBoxFile.Text = OpenFileDialog1.FileName
            Me.Cursor = Cursors.WaitCursor
            ToolStripStatusLabel1.Text = "Transforming file"
            TransformExcelTable()
            ToolStripStatusLabel1.Text = "Ready"
            Me.Cursor = Cursors.Default
        End If

    End Sub

    Private Sub CopyToolStripButton_Click(sender As Object, e As EventArgs) Handles CopyToolStripButton.Click
        Clipboard.SetText(TextBox1.Text, TextDataFormat.Text)
        MsgBox("Data copied to the clipboard", MsgBoxStyle.Information, "Copy Data")
    End Sub

    Private Sub OpenToolStripButton1_Click(sender As Object, e As EventArgs) Handles OpenToolStripButton1.Click
        Dim myFolderBrowser As FolderBrowserDialog = New FolderBrowserDialog

        If myFolderBrowser.ShowDialog = System.Windows.Forms.DialogResult.OK Then
            ToolStripTextBox1.Text = myFolderBrowser.SelectedPath
        End If
    End Sub

    Private Sub ToolStripButton2_Click(sender As Object, e As EventArgs) Handles ToolStripButton2.Click
        Me.Cursor = Cursors.WaitCursor
        ToolStripStatusLabel1.Text = "Adding borders to files"
        AddBorder2Files()
        ToolStripStatusLabel1.Text = "Ready"
        Me.Cursor = Cursors.Default
    End Sub

    Private Sub AddBorder2Files()
        If (ToolStripTextBox1.Text.Length = 0) Then
            MsgBox("No folder selected", MsgBoxStyle.Information, "Add Border")
            Exit Sub
        End If

        Try
            Dim myDirectoryInfo As New IO.DirectoryInfo(ToolStripTextBox1.Text)
            Dim myFiles As IO.FileInfo() = myDirectoryInfo.GetFiles("*.png")
            TextBox1.Text = "Adding border to files:"

            For Each myFile As IO.FileInfo In myFiles
                TextBox1.Text += vbCrLf + " - " + myFile.FullName.ToString
                AddBorder(myFile.FullName.ToString)
            Next

            TextBox1.Text += vbCrLf + "Command completed successfully"

        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Add Border")
        End Try
    End Sub

    Private Sub AddBorder(i_sFileName As String)
        Try
            Dim myBitmap1 As Bitmap = Bitmap.FromFile(i_sFileName)
            Dim myGraphics As Graphics = Graphics.FromImage(myBitmap1)

            Dim borderColor As Color = System.Drawing.Color.FromArgb(195, 195, 195)
            Dim mypen As New Pen(borderColor, 1)
            myGraphics.DrawRectangle(mypen, 0, 0, myBitmap1.Width - 1, myBitmap1.Height - 1)
            myGraphics.Dispose()
            myGraphics = Nothing

            Dim myBitmap2 As Bitmap = New Bitmap(myBitmap1)
            myBitmap1.Dispose()
            myBitmap1 = Nothing

            myBitmap2.Save(i_sFileName)
            myBitmap2.Dispose()
            myBitmap2 = Nothing
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    Private Sub ToolStripButton3_Click(sender As Object, e As EventArgs) Handles ToolStripButton3.Click
        Me.Cursor = Cursors.WaitCursor
        ToolStripStatusLabel1.Text = "Renaming files"
        Files2Lower()
        ToolStripStatusLabel1.Text = "Ready"
        Me.Cursor = Cursors.Default
    End Sub

    Private Sub Files2Lower()
        If (ToolStripTextBox1.Text.Length = 0) Then
            MsgBox("No folder selected", MsgBoxStyle.Information, "Add Border")
            Exit Sub
        End If

        Try
            Dim myDirectoryInfo As New IO.DirectoryInfo(ToolStripTextBox1.Text)
            Dim myFiles As IO.FileInfo() = myDirectoryInfo.GetFiles()
            TextBox1.Text = "Renaming files:"

            For Each myFile As IO.FileInfo In myFiles
                TextBox1.Text += vbCrLf + " - " + myFile.FullName.ToString
                Dim sOldName As String = myFile.FullName.ToString
                Dim sNewName As String = myFile.FullName.ToString.ToLower
                Rename(sOldName, sNewName)
            Next

            TextBox1.Text += vbCrLf + "Command completed successfully"

        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Add Border")
        End Try
    End Sub

    Private Sub ToolStripButton4_Click(sender As Object, e As EventArgs) Handles ToolStripButton4.Click
        Me.Cursor = Cursors.WaitCursor
        ToolStripStatusLabel1.Text = "Starting file conversion"
        FilesConvert()
        ToolStripStatusLabel1.Text = "Ready"
        Me.Cursor = Cursors.Default
    End Sub

    Private Sub FilesConvert()
        If (ToolStripTextBox1.Text.Length = 0) Then
            MsgBox("No folder selected", MsgBoxStyle.Information, "Add Border")
            Exit Sub
        End If

        Try
            Dim myDirectoryInfo As New IO.DirectoryInfo(ToolStripTextBox1.Text)
            Dim arrSearch As String = "*.bmp|*.jpg"
            TextBox1.Text = "Converting files:"

            For Each sSearch As String In arrSearch.Split("|")
                Dim myFiles As IO.FileInfo() = myDirectoryInfo.GetFiles(sSearch)
                For Each myFile As IO.FileInfo In myFiles
                    TextBox1.Text += vbCrLf + " - " + myFile.FullName.ToString
                    FileConvert(myFile)
                Next
            Next

            TextBox1.Text += vbCrLf + "Command completed successfully"

        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "File Convert")
        End Try
    End Sub

    Private Sub FileConvert(i_curFile As FileInfo)
        Try
            Dim szFilePath As String = i_curFile.FullName.ToString
            Dim szFileName As String = Path.GetFileNameWithoutExtension(i_curFile.Name)
            Dim szTargetFileName As String = i_curFile.Directory.ToString + "\" + szFileName + ".png"
            Dim myBitmap1 As Bitmap = Bitmap.FromFile(szFilePath)

            myBitmap1.Save(szTargetFileName, System.Drawing.Imaging.ImageFormat.Png)
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    Private Sub HelpToolStripButton_Click(sender As Object, e As EventArgs) Handles HelpToolStripButton.Click
        Form2.ShowDialog()

    End Sub

    Private Sub BoxesToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles BoxesToolStripMenuItem.Click
        TextBox1.Text = ">[AZURE.TIP]" + vbCrLf & _
                        ">[AZURE.NOTE] " + vbCrLf & _
                        ">[AZURE.IMPORTANT]"

    End Sub

    Private Sub CodeToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles CodeToolStripMenuItem.Click

    End Sub
End Class
